oracle数据文件(表空间文件)被rm掉了,表空间如何删除 。 |
您所在的位置:网站首页 › oracle 数据文件被删除 › oracle数据文件(表空间文件)被rm掉了,表空间如何删除 。 |
将旧数据服务器的数据库迁移到新的服务器上,新服务器连接存储,在处理表空间的过程中不小心将新服务器上/oracle目录下的u01文件夹删除,于是删除表空间时出现问题,现在就演示一下如何解决这个问题 环境:10gR2 [oracle@db1 ~]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on Wed Dec 21 11:31:18 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP and Data Mining options SQL>
1.查看数据文件:
SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- +DATA/orcl/system01.dbf +DATA/orcl/undotbs01.dbf +DATA/orcl/sysaux01.dbf +DATA/orcl/users01.dbf +DATA/orcl/undotbs02.dbf /oracle/u01/app/oracle/oradata/orcl/SMS.dbf /oracle/u01/app/oracle/oradata/orcl/WEBDB.dbf 7 rows selected. 2.查看数据文件状态SQL> select file#,status,name from v$datafile where name like '%SMS%';
FILE# STATUS ---------- ------- NAME -------------------------------------------------------------------------------- 6 ONLINE /oracle/u01/app/oracle/oradata/orcl/SMS.dbf
1 rows selected. 3.确认操作系统上SMS数据文件已经不存在,u01文件夹都没有[oracle@db1 ~]$ ll /oracle/ 总计 6778872 drwxr-xr-x 2 oracle oinstall 4096 12-16 17:07 bak -rw-rw-r-- 1 oracle oinstall 170 12-19 15:41 IMPORT.LOG 4.此时查看数据文件状态,状态没有异常 sec@ora10g> select file#,status,name from v$datafile where name like '%SMS%'; FILE# STATUS NAME ---------- ------- ------------------------------------------------------------ 6 ONLINE /oracle/u01/app/oracle/oradata/orcl/SMS.dbf 5.手工将误删除的文件offline drop,成功SQL> alter database datafile '/oracle/u01/app/oracle/oradata/orcl/SMS.dbf' offline drop; Database altered. 6.此时,该文件的状态为“RECOVER” SQL> select file#,status,name from v$datafile where name like '%SMS%'; FILE# STATUS NAME ---------- ------- ------------------------------------------------------------ 6 RECOVER /oracle/u01/app/oracle/oradata/orcl/SMS.dbf 7.演示删除表空间,成功。SQL> alter database datafile '/oracle/u01/app/oracle/oradata/orcl/SMS.dbf' offline drop;
Database altered. 8.最后确认,删除得很彻底。
SQL>select file#,status,name from v$datafile where name like '%SMS%';
no rows selected
SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- +DATA/orcl/system01.dbf +DATA/orcl/undotbs01.dbf +DATA/orcl/sysaux01.dbf +DATA/orcl/users01.dbf +DATA/orcl/undotbs02.dbf /oracle/u01/app/oracle/oradata/orcl/WEBDB.dbf 6 rows selected. 9.下面的任务就是继续删除WEBDB |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |